﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using System.Globalization;
using Public;

namespace Dal
{
    public class Notes
    {

        public static DataTable NotesType(string strCon)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;

            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Notes_Type");
            }
            catch
            {
                throw;
            }
            return dt;
        }


        public static DataTable MULTIPLE(string strCon)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;

            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Notes_MULTIPLE");
            }
            catch
            {
                throw;
            }
            return dt;
        }

        public static DataTable Single(string strCon, string urlname)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;
            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@NotesCode", SqlDbType.NVarChar, 1000);
            pars[0].Value = urlname;

            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Notes_SINGLE_URL", pars);
            }
            catch
            {
                throw;
            }
            return dt;
        }

        public static DataTable Single(string strCon,int pkid)
        {
            DataTable dt = new DataTable();
            dt.Locale = CultureInfo.InvariantCulture;
            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@PKID", SqlDbType.Int);
            pars[0].Value = pkid;

            try
            {
                dt = SqlHelper.ExecuteDataTable(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Notes_SINGLE_PKID", pars);
            }
            catch
            {
                throw;
            }
            return dt;
        }

        public static int Insert(string strCon, string txbNotesTitle,
                string txbNotesContent,
                string txbKeywords,
                string txbDescription,
                int txbSortOrder,
                int txbIsDisplay,
                int txbIsFoot,
                string txbUrlName)
        {
            int rtn = 0;
            SqlParameter[] pars = new SqlParameter[8];
            pars[0] = new SqlParameter("@NotesTitle", SqlDbType.NVarChar, 20);
            pars[0].Value = txbNotesTitle;
            pars[1] = new SqlParameter("@NotesContent", SqlDbType.NVarChar);
            pars[1].Value = txbNotesContent;
            pars[2] = new SqlParameter("@Keywords", SqlDbType.NVarChar);
            pars[2].Value = txbKeywords;
            pars[3] = new SqlParameter("@Description", SqlDbType.NVarChar);
            pars[3].Value = txbDescription;
            pars[4] = new SqlParameter("@SortOrder", SqlDbType.TinyInt);
            pars[4].Value = txbSortOrder;
            pars[5] = new SqlParameter("@IsDisplay", SqlDbType.Bit);
            pars[5].Value = txbIsDisplay;
            pars[6] = new SqlParameter("@IsFoot", SqlDbType.Bit);
            pars[6].Value = txbIsFoot;
            pars[7] = new SqlParameter("@UrlName", SqlDbType.NVarChar, 20);
            pars[7].Value = txbUrlName;
            try
            {
                rtn = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Notes_INSERT", pars);
            }
            catch
            {
                throw;
            }
            return rtn;
        }

        public static int Update(string strCon,
                string txbNotesTitle,
                string txbNotesContent,
                string txbKeywords,
                string txbDescription,
                int txbSortOrder,
                int txbIsDisplay,
                int txbIsFoot,
                string txbUrlName,
                int txbPKID)
        {
            int rtn = 0;
            SqlParameter[] pars = new SqlParameter[9];
            pars[0] = new SqlParameter("@NotesTitle", SqlDbType.NVarChar, 20);
            pars[0].Value = txbNotesTitle;
            pars[1] = new SqlParameter("@NotesContent", SqlDbType.NVarChar);
            pars[1].Value = txbNotesContent;
            pars[2] = new SqlParameter("@Keywords", SqlDbType.NVarChar);
            pars[2].Value = txbKeywords;
            pars[3] = new SqlParameter("@Description", SqlDbType.NVarChar);
            pars[3].Value = txbDescription;
            pars[4] = new SqlParameter("@SortOrder", SqlDbType.TinyInt);
            pars[4].Value = txbSortOrder;
            pars[5] = new SqlParameter("@IsDisplay", SqlDbType.Bit);
            pars[5].Value = txbIsDisplay;
            pars[6] = new SqlParameter("@IsFoot", SqlDbType.Bit);
            pars[6].Value = txbIsFoot;
            pars[7] = new SqlParameter("@UrlName", SqlDbType.NVarChar, 20);
            pars[7].Value = txbUrlName;
            pars[8] = new SqlParameter("@PKID", SqlDbType.Int);
            pars[8].Value = txbPKID;
            
            try
            {
                rtn = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Notes_UPDATE", pars);
            }
            catch
            {
                throw;
            }
            return rtn;
        }

        public static int UpdateField(string strCon, int pkid, string fieldName, int fieldValue)
        {
            int rtn = 0;
            SqlParameter[] pars = new SqlParameter[3];
            pars[0] = new SqlParameter("@PKID", SqlDbType.SmallInt);
            pars[0].Value = pkid;
            pars[1] = new SqlParameter("@FieldName", SqlDbType.NVarChar, 100);
            pars[1].Value = fieldName;
            pars[2] = new SqlParameter("@FieldValue", SqlDbType.Bit);
            pars[2].Value = fieldValue;
            try
            {
                rtn = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Notes_UPDATE_FIELD", pars);
            }
            catch
            {
                throw;
            }
            return rtn;
        }

        public static int Delete(string strCon, int pkid)
        {
            int rtn = 0;
            SqlParameter[] pars = new SqlParameter[1];
            pars[0] = new SqlParameter("@PKID", SqlDbType.SmallInt);
            pars[0].Value = pkid;
            try
            {
                rtn = SqlHelper.ExecuteNonQuery(DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_Notes_DELETE", pars);
            }
            catch
            {
                throw;
            }
            return rtn;
        }

        public static DataTable SearchSigle(string strCon,string whereStr) 
        {
            DataTable dt = new DataTable();
            SqlParameter[] pars = { 
                                    new SqlParameter("@WhereStr",whereStr)
                                  };
            try
            {
                dt = SqlHelper.ExecuteDataTable(Public.DataProtector.DecryptString(strCon), CommandType.StoredProcedure, "SP_NotesInfo_Search_SINGLE", pars);
            }
            catch (Exception)
            {
                throw;
            }
            return dt;
        }

    }
}
